/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 *
 * @author pyty
 */
public class MediumManagerImpl implements MediumManager {

    private static final Logger logger = Logger.getLogger(
            MediumManagerImpl.class.getName());

    /*public MediumManagerImpl() {
        try {
            conn = DriverManager.getConnection("jdbc:derby://localhost:1527/mediumEvidence", "name", "pass");
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when connecting to DB", ex);
            throw new RuntimeException("Error when connecting to DB", ex);
        }
    } */

    private DataSource dataSource;

    public void setDataSource(DataSource ds) {
        this.dataSource = ds;
    }
     public void tryCreateTables() {
            checkDataSource();
        try {
            DBUtils.tryCreateTables(dataSource);
        } catch (SQLException ex) {
            throw new RuntimeException("Error when trying to create tables", ex);
        }
    }
     private void checkDataSource() {
        if (dataSource == null) {
            throw new IllegalStateException("DataSource is not set");
        }
    }

    public void createMedium(Medium medium) {
        if (medium == null) {
            throw new IllegalArgumentException("medium");
        }
        if (medium.getId() != null) {
            throw new IllegalArgumentException("medium id is already set");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(
                    "INSERT INTO MEDIUM (NAME,AUTHOR,TYPE,DESCRIPTION,CLIENT_ID) VALUES (?,?,?,?,?)",
                    Statement.RETURN_GENERATED_KEYS);
            st.setString(1, medium.getName());
            st.setString(2, medium.getAuthor());
            st.setString(3, medium.getType());
            st.setString(4, medium.getDescription());
            st.setLong(5, 0);

            int count = st.executeUpdate();
            assert count == 1;             //************************ ???????

            Long id = getId(st.getGeneratedKeys());
            medium.setId(id);

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when inserting medium into DB", ex);
            throw new RuntimeException("Error when inserting medium into DB", ex);
        } finally {
            /*if (st != null) {
                try {
                    st.close();
                } catch (SQLException ex) {
                    logger.log(Level.SEVERE, "Error when closing connection", ex);
                }
            }*/
            DBUtils.closeQuietly(conn);
        }
    }

    public void deleteMedium(Medium medium) {
        if (medium == null) {
            throw new IllegalArgumentException("medium");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement("SELECT client_id FROM medium WHERE id=?");
            st.setLong(1, medium.getId());

            ResultSet rs = st.executeQuery();
            Long customerId = null;

            if (rs.next()) {
                customerId = rs.getLong("client_id");
                assert !rs.next();
            } else {
                throw new IllegalArgumentException("Medium with id " + medium.getId() + " doesnt exists.");
            }

            if (customerId != 0) {
                throw new IllegalArgumentException("Cannot delete already borrowed medium.");
            }

            st = conn.prepareStatement("DELETE FROM medium WHERE id=?");
            st.setLong(1, medium.getId());
            if (st.executeUpdate() == 0) {
                throw new IllegalArgumentException("medium not found");
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when deleting medium from DB", ex);
            throw new RuntimeException("Error when deleting medium from DB", ex);
        } finally {
            DBUtils.closeQuietly(conn);
        }
    }

    public void updateMedium(Medium medium) {
        if (medium == null) {
            throw new IllegalArgumentException("medium");
        }
        if (medium.getId() == null) {
            throw new IllegalArgumentException("medium id must be specified");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(
                    "UPDATE MEDIUM SET NAME=?, AUTHOR=?, TYPE=?, DESCRIPTION=? WHERE ID=?",
                    Statement.RETURN_GENERATED_KEYS);
            st.setString(1, medium.getName());
            st.setString(2, medium.getAuthor());
            st.setString(3, medium.getType());
            st.setString(4, medium.getDescription());
            st.setLong(5, medium.getId());

            int count = st.executeUpdate();
            assert count == 1;

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when inserting medium into DB", ex);
            throw new RuntimeException("Error when inserting medium into DB", ex);
        } finally {
            DBUtils.closeQuietly(conn);
        }
    }

    public Medium findMediumById(Long id) {
        if (id == null) {
            throw new IllegalArgumentException("id");
        }
        Connection conn = null;
        
        try {
            conn = dataSource.getConnection();
            PreparedStatement st = conn.prepareStatement(
                    "SELECT name, author, type, description FROM medium WHERE ID = ?");
            st.setLong(1, id);

            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                Medium result = new Medium();
                result.setId(id);
                result.setName(rs.getString("name"));
                result.setAuthor(rs.getString("author"));
                result.setType(rs.getString("type"));
                result.setDescription(rs.getString("description"));
                assert !rs.next();
                return result;
            } else {
                return null;
            }

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting medium from DB", ex);
            throw new RuntimeException("Error when getting medium from DB", ex);
        } finally {
           DBUtils.closeQuietly(conn);
        }
    }

    public List<Medium> findAllMediums() {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement("SELECT * FROM medium");
            ResultSet rs = st.executeQuery();
            List<Medium> found = new ArrayList<Medium>(); //*********** ??? vysledky su v nahodonom poradi
            while (rs.next()) {
                Medium result = new Medium();
                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setAuthor(rs.getString("author"));
                result.setType(rs.getString("type"));
                result.setDescription(rs.getString("description"));
                found.add(result);
            }
            return Collections.unmodifiableList(found);
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting all mediums from DB", ex);
            throw new RuntimeException("Error when getting all mediums from DB", ex);
        } finally {
            DBUtils.closeQuietly(conn);
        }
    }
    
    public List<Medium> searchMediumsByString(String fulltext) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = dataSource.getConnection();
            
            st = conn.prepareStatement("SELECT * FROM MEDIUM WHERE "
                    +   " LOWER(name) LIKE ? "
                    + "OR LOWER(author) LIKE ? "
                    + "OR LOWER(description) LIKE ? ");
            st.setString(1, "%" + fulltext + "%");
            st.setString(2, "%" + fulltext + "%");
            st.setString(3, "%" + fulltext + "%");
            ResultSet rs = st.executeQuery();
            List<Medium> found = new ArrayList<Medium>();
            while (rs.next()) {
                Medium result = new Medium();
                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setAuthor(rs.getString("author"));
                result.setType(rs.getString("type"));
                result.setDescription(rs.getString("description"));
                found.add(result);
            }
            return Collections.unmodifiableList(found);
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting all mediums from DB", ex);
            throw new RuntimeException("Error when getting all mediums from DB", ex);
        } finally {
            DBUtils.closeQuietly(conn);
        }
        //;
    }

    private static Long getId(ResultSet keys) throws SQLException {
        if (keys.getMetaData().getColumnCount() != 1) {
            throw new IllegalArgumentException("Given ResultSet contains more columns");
        }
        if (keys.next()) {
            Long result = keys.getLong(1);
            if (keys.next()) {
                throw new IllegalArgumentException("Given ResultSet contains more rows");
            }
            return result;
        } else {
            throw new IllegalArgumentException("Given ResultSet contain no rows");
        }
    }
}